import time
from datetime import datetime
import requests
import pymysql
import wget
import base64
import os
from lxml import etree
import traceback

headers = {
    "User-Agent":
        "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.149 Safari/537.36",
}


def dkgw_main():
    errorNum = 0
    sqlSet = set()

    print('首页左侧图片', datetime.now(), '开始')
    try:
        l = dk_sy()
        for i in l:
            if 'https' not in str(i[2]):
                i[2] = str(i[2]).replace('http', 'https')
            imgUrl = str(i[2])
            wget.download(imgUrl, 'tets.jpg')
            f = open("./tets.jpg", "rb")
            ls_f = 'data:image/jpeg;base64,' + str(base64.b64encode(f.read()), 'utf-8')
            f.close()
            os.remove(r"./tets.jpg")
            if 'https' not in str(i[1]):
                i[1] = str(i[1]).replace('http', 'https')

            sql = makesql1(i[0], i[1], ls_f,i[3])
            sqlSet.add(sql)
        print('首页左侧图片', datetime.now(), '结束')
    except Exception as e:
        print(e, '首页左侧图片', '出错')
        errorNum += 1
        print(traceback.print_exc())
    print('资讯中心', datetime.now(), '开始')
    s = {'gsxw': '公司新闻', 'gsgg': '公司公告', 'xyxx': '行业信息'}
    try:
        ll = dk_zx()
        for k in ll.keys():
            for i in ll[k]:
                c = check2(i[1], s[k])
                if len(c) == 0:
                    sql = makesql2(i[0], i[1], i[2], s[k])
                    sqlSet.add(sql)
        print('资讯中心', datetime.now(), '结束')
    except Exception as e:
        print(e, '资讯中心', '出错')
        errorNum += 1
        print(traceback.print_exc())
    print('首页右侧新闻', datetime.now(), '开始')
    try:
        l2 = dk_sy2()
        for i in l2:
            c = check2(i[1], '公司新闻')
            if len(c) == 0:
                sql = makesql2(i[0], i[1], i[2], '公司新闻')
                sqlSet.add(sql)
        print('首页右侧新闻', datetime.now(), '结束')
    except Exception as e:
        print(e, '首页右侧新闻', '出错')
        errorNum += 1
        print(traceback.print_exc())
    if errorNum == 0 and len(sqlSet) != 0:
        # 都无报错才清空并更新数据
        savesql('truncate behc_dkgw_sy')
        num = 0
        for sql in sqlSet:
            savesql(sql)
            num += 1
        log = "更新" + str(num) + "条入库behc_dkgw_zxzx"
        savesql(
            'INSERT INTO behc_log (name, log, update_time) VALUES ("电控官网", "' + log + '", "' + datetime.strftime(
                datetime.now(), '%Y-%m-%d %H:%M:%S') + '")')


# 左侧带图片
def dk_sy():
    coun = 0
    r = requests.get(url='https://www.behc.com.cn', headers=headers)
    r.encoding = 'utf-8'
    root = etree.HTML(r.text)
    content_list = root.xpath('//a[@class="item"]')
    l = []
    for c in content_list:
    
    
        title = c.xpath('./span/text()')[0]
        if 'https://www.behc.com.cn' in str(c.xpath('./@href')[0]):
            href = c.xpath('./@href')[0]
        else:
            href = 'https://www.behc.com.cn' + c.xpath('./@href')[0][1:]
        img = 'https://www.behc.com.cn' + c.xpath('./img/@src')[0][1:]
        # 进入到子页面拿时间
        #if href.split('/')[4]== 'gsxw':
            #child_r = requests.get(url=href, headers=headers)
            #child_r.encoding = 'utf-8'
            #child_root = etree.HTML(child_r.text)
            #time_str = child_root.xpath('//p[@class="content_infor"]')[0].xpath('./span/text()')[0].replace("发布时间：",'')
        #else:
        # time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
            #time_str = ""
        #l.append([title, href, img,time_str])
        l.append([title, href, img,coun])

        coun += 1
    return l



# 右侧新闻
def dk_sy2():
    r = requests.get(url='https://www.behc.com.cn', headers=headers)
    r.encoding = 'utf-8'
    root = etree.HTML(r.text)
    content_list = root.xpath('//div[@class="company_news"]/ul/li')
    l2 = []
    for c in content_list:
        title = c.xpath('./a/text()')[0]
        href = 'https://www.behc.com.cn' + c.xpath('./a/@href')[0][1:]
        time = c.xpath('./span/text()')[0]
        l2.append([title, href, time])
    return l2


# 资讯中心
def dk_zx():
    us = ['gsxw', 'gsgg', 'xyxx']
    ll = {}
    for u in us:
        url = 'https://www.behc.com.cn/infor/' + u
        r = requests.get(url=url, headers=headers)
        r.encoding = 'utf-8'
        root = etree.HTML(r.text)
        content_list = root.xpath('//div[@id="gsxw"]/ul/li')
        l3 = []
        for c in content_list:
            title = c.xpath('./div/a/text()')[0]
            href = 'https://www.behc.com.cn/infor/' + u + c.xpath(
                './div/a/@href')[0][1:]
            time = c.xpath('./span/text()')[0]
            l3.append([title, href, time])
        ll[u] = l3
    return ll


# SQL保存
def savesql(sql):
    con = None
    cur = None
    result = '执行成功'
    try:
        con = pymysql.connect(host="192.168.155.141",
                              port=3306,
                              user="root",
                              passwd="fD@LCVby2WJI",
                              database="behc_python",
                              charset='utf8')
        if con:
            print("链接成功")
            # 获取操作数据库的游标
            cur = con.cursor()
            # 执行sql语句
            cur.execute(sql)
            con.commit()
        else:
            print("链接失败")
    except Exception as e:
        result = e
    finally:
        if con:
            if cur:
                cur.close()
            con.close()
    print(result)


# 带图片SQL
def makesql1(t, h, i,j):
    sql = 'INSERT INTO behc_dkgw_sy (title, href, img,sort) VALUES ('
    sql += "'" + t + "', '" + h + "', '" + i + "',  "+str(j)+" ); "
    return sql


# 新闻资讯SQL
def makesql2(t, h, t2, s):
    if 'https' not in h:
        h = h.replace('http', 'https')
    h = h.replace('infor/gsxw./../', '')
    sql = 'INSERT INTO behc_dkgw_zxzx (title, href, `time`, sort) VALUES ('
    sql += "'" + t + "', '" + h + "', '" + t2 + "', '" + s + "'); "
    return sql


# 检查是否继续爬取
def check(sql):
    con = None
    cur = None
    try:
        con = pymysql.connect(host="192.168.155.141",
                              port=3306,
                              user="root",
                              passwd="fD@LCVby2WJI",
                              database="behc_python",
                              charset='utf8')
        if con:
            print("链接成功")
            # 获取操作数据库的游标
            cur = con.cursor()
            # 执行sql语句
            cur.execute(sql)
            datas = cur.fetchall()
        else:
            print("链接失败")
    except Exception as e:
        print(e)
    finally:
        if con:
            if cur:
                cur.close()
            con.close()
    return datas


# 新闻资讯SQL
def check2(h, s):
    if 'https' not in h:
        h = h.replace('http', 'https')
    h = h.replace('infor/gsxw./../', '')
    sql = "SELECT * FROM behc_dkgw_zxzx where"
    sql += " href = '" + h + "' and sort = '" + s + "';"
    return check(sql)


dkgw_main()

